package com.yuncheng.spcyApi.utils;

import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.yuncheng.spcyApi.constant.common.BusinessConstant;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.dom4j.Document;
import org.dom4j.Element;

import cn.hutool.core.bean.DynaBean;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import org.dom4j.io.SAXReader;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.stereotype.Component;

/**
 * 实现按xml模板自动化导入导出的工具类
 * @author lmy
 * <?xml version="1.0" encoding="UTF-8"?>
 * <a><bean></bean>
 * <param key=""></param></a>
 */
@Component
public class MyExcelUtils {

    @javax.annotation.Resource
    private BusinessConstant bussinessConstant;

    public List<Map<String,Object>> paresExclToMapList(String xmlUrl,File upload) throws Exception{
        List<Map<String,Object>> resList = new ArrayList<Map<String,Object>>();
        SAXReader reader = new SAXReader();
//		ClassPathResource classPathResource = new ClassPathResource(BussinessConstant.EXCEL_TEMPLATE_XML_ROOT_URL+"/"+xmlUrl);
        Resource resource = new ClassPathResource(BusinessConstant.EXCEL_TEMPLATE_XML_ROOT_URL+"/"+xmlUrl);
        File file = new File(bussinessConstant.TEMPORARY_UPLOAD_MKDIR_ROOT+"/"+BusinessConstant.EXCEL_TEMPLATE_XML_ROOT_URL+"/"+xmlUrl);
        FileUtils.copyToFile(resource.getInputStream(),file);
//		File file = classPathResource.getFile();
        Document document = reader.read(file);
//		Document document = reader.read(BussinessConstant.EXCEL_TEMPLATE_XML_ROOT +"/"+ xmlUrl);
        Element root = document.getRootElement();
        List<Element> elements = root.elements();

        Map<String,String> xmlMap = new HashMap<String,String>();
        for(Element e:elements){
            if("param".equals(e.getName())){
                String key = e.attribute("key").getValue().trim();
                String val = e.getTextTrim();
                xmlMap.put(key, val);
            }
        }
        ExcelReader excelReader = ExcelUtil.getReader(upload,0);
        List<Map<String,Object>> excelMapList = excelReader.readAll();
        for(Map<String,Object> excelMap:excelMapList){
            Map<String,Object> retMap = new HashMap<String,Object>();
            for(Map.Entry<String, String> entry : xmlMap.entrySet()){
                String mapKey = entry.getKey();

                String fieldname = entry.getValue();
                if (excelMap.get(mapKey) instanceof String && excelMap.get(mapKey) != null){
                    retMap.put(fieldname, ((String) excelMap.get(mapKey)).trim());
                }else {
                    retMap.put(fieldname, excelMap.get(mapKey));
                }
            }
            resList.add(retMap);
        }
        excelReader.close();
        return resList;
    }

    private static String getBeanName(Document xmlTemplate) throws Exception {
        if (xmlTemplate == null) {
            throw new Exception("导入/导出模板不能为空！");
        }
        String beanName = "";
        Element root = xmlTemplate.getRootElement();
        List<Element> elements = root.elements();
        for (Element e : elements) {
            if ("bean".equals(e.getName())) {
                beanName = e.getTextTrim();
                break;
            }
        }
        if (StringUtils.isBlank(beanName)) {
            throw new Exception("导入/导出模板格式不对，应设置标签<bean>！");
        }
        return beanName;
    }

    public static Map<String, String> getXmlMap(Document xmlTemplate) throws Exception {
        Map<String, String> xmlMap = new HashMap<String, String>();
        Element root = xmlTemplate.getRootElement();
        List<Element> elements = root.elements();
        for (Element e : elements) {
            if ("param".equals(e.getName())) {
                String key = e.attribute("key").getValue().trim();
                String val = e.getTextTrim();
                xmlMap.put(key, val);
            }
        }
        if (xmlMap.size() == 0) {
            throw new Exception("导入/导出模板格式不对，应设置标签<param>！");
        }
        return xmlMap;
    }

    public static List<Object> getTitleList(Document xmlTemplate) throws Exception {
        List<Object> titleList = new ArrayList<>();
        Element root = xmlTemplate.getRootElement();
        List<Element> elements = root.elements();
        for (Element e : elements) {
            if ("param".equals(e.getName())) {
                String key = e.attribute("key").getValue().trim();
                titleList.add(key);
            }
        }
        if (titleList.size() == 0) {
            throw new Exception("导入/导出模板格式不对，应设置标签<key>！");
        }
        return titleList;
    }

    private static List<String> getFieldList(Document xmlTemplate) throws Exception {
        List<String> fieldList = new ArrayList<>();
        Element root = xmlTemplate.getRootElement();
        List<Element> elements = root.elements();
        for (Element e : elements) {
            if ("param".equals(e.getName())) {
                String val = e.getTextTrim();
                fieldList.add(val);
            }
        }
        if (fieldList.size() == 0) {
            throw new Exception("导入/导出模板格式不对，应设置标签<param>的值！");
        }
        return fieldList;
    }

    /**
     * 将上传文件转换成对象集合
     *
     * @param xmlTemplate
     *            导入模板
     * @param uploadFile
     *            上传文件
     * @return
     * @throws Exception
     */
    public static List<Object> convertUploadFileToXmlTemplateList(Document xmlTemplate, File uploadFile) throws Exception {
        List<Object> resList = new ArrayList<Object>();

        String beanName = getBeanName(xmlTemplate);
        Map<String, String> xmlMap = getXmlMap(xmlTemplate);

        ExcelReader excelReader = ExcelUtil.getReader(uploadFile, 0);
        List<Map<String, Object>> excelMapList = excelReader.readAll();
        for (Map<String, Object> excelMap : excelMapList) {
            Class obj = Class.forName(beanName);
            Object bean = obj.newInstance();
            Class cla = bean.getClass();
            for (Map.Entry<String, String> entry : xmlMap.entrySet()) {
                String mapKey = entry.getKey();
                String mapValue = entry.getValue();
                if (StringUtils.isBlank(mapValue))
                    continue;
                if (StringUtils.isBlank(mapKey))
                    continue;
                Object val = excelMap.get(mapKey);
                if (val == null || StringUtils.isBlank(val.toString()))
                    continue;
                Field f = cla.getDeclaredField(mapValue);
                PropertyDescriptor pd = new PropertyDescriptor(f.getName(), cla);
                Method wm = pd.getWriteMethod();
                Class<?>[] t = wm.getParameterTypes();
                if (t[0].getSimpleName().equals("Integer")) {
                    try {
                        wm.invoke(bean, Integer.parseInt(val.toString()));
                    } catch (Exception e) {
                        throw new Exception("[" + mapKey + "]格式不对,应为数值！");
                    }
                } else if (t[0].getSimpleName().equals("Date")) {
                    try {
                        if (!(val instanceof Date) && StringUtils.isNotBlank(val.toString())) {
                            if (!"/".equals(val.toString())) {
                                val = DateUtil.parse(val.toString());
                                wm.invoke(bean, val);
                            }
                        } else {
                            wm.invoke(bean, val);
                        }
                    } catch (Exception e) {
//                        System.out.println(mapValue + "日期格式不对！");
                        throw new Exception("[" + mapKey + "]日期格式不对！");
                    }
                } else if (t[0].getSimpleName().equals("Double")) {
                    wm.invoke(bean, Double.parseDouble(val.toString()));
                } else if (t[0].getSimpleName().equals("Float")) {
                    wm.invoke(bean, Float.parseFloat(val.toString()));
                } else if (t[0].getSimpleName().equals("String")) {
                    val = StrUtil.removeAllLineBreaks(val.toString().trim());
                    wm.invoke(bean, val.toString());
                } else {
                    wm.invoke(bean, val.toString());
                }
            }
            resList.add(bean);
        }
        return resList;
    }

    /**
     * 输出导入/导出模板文件
     *
     * @param xmlTemplate
     *            导入/导出模板
     * @param out
     *            HttpServletResponse的OutputStream
     */
    public static void outputXmlTemplateFile(Document xmlTemplate, OutputStream out) throws Exception {
        List<Object> titleList = getTitleList(xmlTemplate);
        List<List<Object>> rows = new ArrayList<>();
        rows.add(titleList);
        ExcelWriter writer = ExcelUtil.getWriter();
        writer.write(rows, true);
        writer.flush(out, true);
        out.flush();
        writer.close();
        IoUtil.close(out);
    }

    /**
     * 将对象集合输出到Excl文件
     *
     * @param xmlTemplate
     *            导出模板
     * @param out
     *            HttpServletResponse的OutputStream
     */
    public static void outputBeansToExclFile(Document xmlTemplate, List<Object> beans, OutputStream out) throws Exception {

        String beanName = getBeanName(xmlTemplate);
        List<Object> titleList = getTitleList(xmlTemplate);
        List<String> fieldList = getFieldList(xmlTemplate);

        Class obj = Class.forName(beanName);
        ExcelWriter writer = ExcelUtil.getWriter();
        List<List<Object>> rows = new ArrayList<>();
        rows.add(titleList);
        for (Object o : beans) {
            List<Object> valList = new ArrayList<>();
            DynaBean bean = DynaBean.create(o);
            for (String field : fieldList) {
                if (StringUtils.isNotBlank(field)) {
                    valList.add(bean.get(field));
                } else {
                    valList.add("");
                }
            }
            rows.add(valList);
        }
        writer.write(rows, true);
        writer.flush(out, true);
        out.flush();
        writer.close();
        IoUtil.close(out);
    }

    public void buildImpModel(String xmlUrl,OutputStream out) throws Exception{
        SAXReader reader = new SAXReader();
//		xmlUrl = MyExclUtil.class.getResource("/").getPath()+"com\\yuncheng\\spcy\\exclInOutTemplet\\"+xmlUrl;
//		ClassPathResource classPathResource = new ClassPathResource(BussinessConstant.EXCEL_TEMPLATE_XML_ROOT_URL+"/"+xmlUrl);
//		File file = classPathResource.getFile();
        Resource resource = new ClassPathResource(BusinessConstant.EXCEL_TEMPLATE_XML_ROOT_URL+"/"+xmlUrl);
        File file = new File(bussinessConstant.TEMPORARY_UPLOAD_MKDIR_ROOT+"/"+BusinessConstant.EXCEL_TEMPLATE_XML_ROOT_URL+"/"+xmlUrl);
        FileUtils.copyToFile(resource.getInputStream(),file);
        Document document = reader.read(file);
//		Document document = reader.read(BussinessConstant.EXCEL_TEMPLATE_XML_ROOT+"/"+xmlUrl);
        Element root = document.getRootElement();
        List<Element> elements = root.elements();
        List<String> titleList = new ArrayList<>();
        for(Element e:elements){
            if("param".equals(e.getName())){
                String key = e.attribute("key").getValue().trim();
                titleList.add(key);
            }
        }
        List<List<String>> rows = new ArrayList<>();
        rows.add(titleList);
        ExcelWriter writer = ExcelUtil.getWriter();
        writer.write(rows, true);
        writer.flush(out, true);
        out.flush();
        writer.close();
        IoUtil.close(out);
    }

    /**
     * 将对象集合输出到Excl文件
     *
     * @param xmlTemplateName
     *            导出模板
     * @param out
     *            HttpServletResponse的OutputStream
     */
    public void outputListToExclFile(String xmlTemplateName, List<Object> beans, OutputStream out) throws Exception {
//		ClassPathResource classPathResource = new ClassPathResource(BussinessConstant.EXCEL_TEMPLATE_XML_ROOT_URL+"/"+xmlTemplateName);
//		File file = classPathResource.getFile();
        Resource resource = new ClassPathResource(BusinessConstant.EXCEL_TEMPLATE_XML_ROOT_URL+"/"+xmlTemplateName);
        File file = new File(bussinessConstant.TEMPORARY_UPLOAD_MKDIR_ROOT+"/"+BusinessConstant.EXCEL_TEMPLATE_XML_ROOT_URL+"/"+xmlTemplateName);
        FileUtils.copyToFile(resource.getInputStream(),file);
        SAXReader reader = new SAXReader();
        Document xmlTemplate = reader.read(file);
        List<Object> titleList = getTitleList(xmlTemplate);
        List<String> fieldList = getFieldList(xmlTemplate);

        ExcelWriter writer = ExcelUtil.getWriter();
        List<List<Object>> rows = new ArrayList<>();
        rows.add(titleList);
        for (Object o : beans) {
            List<Object> valList = new ArrayList<>();
            DynaBean bean = DynaBean.create(o);
            for (String field : fieldList) {
                if (StringUtils.isNotBlank(field)) {
                    valList.add(bean.get(field));
                } else {
                    valList.add("");
                }
            }
            rows.add(valList);
        }
        writer.write(rows, true);
        writer.flush(out, true);
        out.flush();
        writer.close();
        IoUtil.close(out);
    }
}
